package globalVisionEntertainment.nba;

import gqqnbig.StringHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.*;

import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

public class Program
{

	private static SimpleDateFormat onCourtTimeFormat;

	static
	{

		// dateFormat.setTimeZone(new SimpleTimeZone(0, "UTC"));
		TimeZone.setDefault(new SimpleTimeZone(0, "UTC"));
		onCourtTimeFormat = new SimpleDateFormat("H:m:ss");
	}

	public static void main(String[] args) throws Exception
	{

		String gameId = args[0];

		try
		{
			run(gameId);
		}
		catch (Exception e)
		{
			System.err.println("Error in loading game " + gameId);
			System.err.println("Error Message: " + e.getMessage());
			e.printStackTrace();
		}
	}

	private static void run(String gameId) throws ClassNotFoundException, Exception
	{

		System.out.println("Importing " + gameId);

		EspnGameRecord gameRecord = new EspnGameRecord(gameId);

		Class.forName("com.mysql.jdbc.Driver");
		Connection conn;
		try
		{
			conn = Database.getConnection();
			conn.setAutoCommit(true);

			// conn.setAutoCommit(true);

			if (BasketballRules.normalSectionCount != 4)
				throw new UnsupportedOperationException("The database scheme assumes 4 sections in a game.");

			Game game = gameRecord.getGameInfo();
			SimpleDateFormat mySqlDateFormat = new SimpleDateFormat("yyyy-MM-dd");
			mySqlDateFormat.setTimeZone(new SimpleTimeZone(0, "UTC"));

			insertGameToDatabase(gameId, conn, game, mySqlDateFormat);

			try
			{
				storeGameStatistic(conn, gameRecord, GameParty.Home);
				storeGameStatistic(conn, gameRecord, GameParty.Visitor);
			}
			catch (SQLException e)
			{
				if (e.getErrorCode() == 1062)
					System.err.println("Cannot import duplicate statistic. Have you imported game " + gameId + " ?");

				System.err.print("SQL Error: ");
				System.err.println(e.getMessage());
			}
			catch (Exception e)
			{
				e.printStackTrace();
				// TODO: handle exception
			}
			finally
			{
				conn.close();
			}
		}
		catch (SQLException e)
		{
			System.err.println("Failed to connect to mysql database. Is the service running?");
			System.err.println(e.toString());
		}
	}

	private static void insertGameToDatabase(String gameId, Connection conn, Game game, SimpleDateFormat mySqlDateFormat)
			throws SQLException
	{
		try
		{
			// @formatter:off
			String sql = String.format("insert into Game \n" + "values(\"%s\", \"%s\", \"%s\", \"%s\")", gameId,
					game.homeTeam, game.visitingTeam, mySqlDateFormat.format(game.date));
			// @formatter:on

			Statement statement = conn.createStatement();
			statement.execute(sql);
		}
		catch (SQLException e)
		{
			if (e.getErrorCode() == 1062)
			{
				System.out.println("Game metadata " + gameId + " already exists. Cannot import duplicate data.\n"
						+ "Continue to import player statistic.");

				System.out.print("SQL Error: ");
				System.out.println(e.getMessage());
			}
			else
				throw e;

		}
	}

	private static void storeGameStatistic(Connection conn, EspnGameRecord gameRecord, GameParty party) throws Exception
	{
		ArrayList<PlayerGameStatistic> players = gameRecord.getPlayers(party);
		writeToDB(conn, gameRecord, party, players);

		for (int i = 1; i <= BasketballRules.normalSectionCount; i++)
		{
			ArrayList<PlayerInQuarterStatistic> inQuarterStatistic = gameRecord.getInQuarterStatistic(i, party);

			String sql = "insert into Player_In_Quarter_Statistic(PlayerName, Game, Quarter, EnterCourt, LeaveCourt, EnterMyScore, EnterRivalScore, LeaveMyScore, LeaveRivalScore) values";
			String sqlValue = "(?,?,?,?,?,?,?,?,?)";
			sql += StringHelper.repeat(sqlValue, ",\n", inQuarterStatistic.size());

			PreparedStatement statement = conn.prepareStatement(sql);
			int index = 1;
			for (PlayerInQuarterStatistic piqs : inQuarterStatistic)
			{
				statement.setString(index++, piqs.playerName);
				statement.setString(index++, gameRecord.gameId);
				statement.setInt(index++, i);
				statement.setString(index++, onCourtTimeFormat.format(piqs.getEnterTime()));
				statement.setString(index++, onCourtTimeFormat.format(piqs.getLeaveTime()));
				statement.setInt(index++, piqs.enterMyScore);
				statement.setInt(index++, piqs.enterRivalScore);
				statement.setInt(index++, piqs.leaveMyScore);
				statement.setInt(index++, piqs.leaveRivalScore);

			}
			statement.execute();
		}

	}

	private static void writeToDB(Connection conn, EspnGameRecord gameRecord, GameParty gameParty, ArrayList<PlayerGameStatistic> players)
			throws Exception, SQLException
	{
		String sql = "insert into playerstatistic(PlayerName,Owner,Game,IsStarter,`Match Order`,Position) values";
		String sqlValue = "(?,?,?,?,?,?),\n";

		String owner = gameParty == GameParty.Visitor ? gameRecord.getGameInfo().visitingTeam : gameRecord.getGameInfo().homeTeam;

		for (PlayerGameStatistic player : players)
			sql += sqlValue;
		sql = sql.substring(0, sql.length() - 2);

		PreparedStatement statement = conn.prepareStatement(sql);

		int index = 1;

		for (PlayerGameStatistic player : players)
		{
			statement.setString(index++, player.playerName);
			statement.setString(index++, owner);
			statement.setString(index++, gameRecord.gameId);
			statement.setBoolean(index++, player.isStarter);
			statement.setInt(index++, player.virtualPosition);
			statement.setString(index++, player.position);
		}

		statement.execute();
	}

	static int isEnoughPlayersOnCourt(ArrayList<PlayerInQuarterStatistic> playerStatistics)
	{
		int count = 0;
		for (PlayerInQuarterStatistic ps : playerStatistics)
		{
			if (ps.getLeaveTime().getTime() == 0)
				count++;
		}
		return count;

	}

	static ArrayList<String> getStarters(Element teamBlock)
	{
		Elements nameElements = teamBlock.select("tr:lt(5)>td:eq(0) a");

		ArrayList<String> starter = new ArrayList<String>(5);
		for (Element element : nameElements)
		{
			starter.add(element.text());
		}

		assert starter.size() <= 5;

		return starter;
	}

	/**
	 * Find a player from playerStatistics whose leave time is null.
	 * 
	 * @param playerName
	 * @param playerStatistics
	 * @return
	 */
	static PlayerInQuarterStatistic getOnCourtPlayers(String playerName, List<PlayerInQuarterStatistic> playerStatistics)
	{
		boolean hasThePlayer = false;
		for (PlayerInQuarterStatistic player : playerStatistics)
		{
			if (player.playerName.equals(playerName))
			{
				if (player.getLeaveTime() == null)
					return player;
				else
					hasThePlayer = true;
			}
		}

		if (hasThePlayer)
			throw new IllegalArgumentException(String.format(
					"Play-by-play contains error. %s is not on court, but someone enters for him.", playerName));

		// cannot find that player. Heuristically add the player to
		// playerStatistics.
		PlayerInQuarterStatistic ps = new PlayerInQuarterStatistic(playerName, new Date(BasketballRules.sectionLength * 60 * 1000));

		playerStatistics.add(ps);
		return ps;

	}
}
